﻿//*******************************************************************/
//** 编 写 人：lardust-PC AFCodeAI
//** 编写日期：07/08/2013 14:01:27
//** 实现功能：
//** 修 改 人：
//** 修改日期：
//*******************************************************************/
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CrazyTalk.Common;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;

namespace CrazyTalk.MSSQLDAL
{
	public class CommentBaseDAL: ICommentBaseDAL
	{   		     
		
		/// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(CommentBase model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CommentBase(");
            strSql.Append("TopicBaseId,ReplyCommentBaseId,Title,Content,CreatorId,CreateDate,Editor,EditDate)");
            strSql.Append(" values (");
            strSql.Append("@TopicBaseId,@ReplyCommentBaseId,@Title,@Content,@CreatorId,@CreateDate,@Editor,@EditDate)");  
            strSql.Append(";select @@IDENTITY");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = model.TopicBaseId },
				new SqlParameter("@ReplyCommentBaseId", SqlDbType.Int,4){ Value = model.ReplyCommentBaseId },
				new SqlParameter("@Title", SqlDbType.NVarChar,50){ Value = model.Title },
				new SqlParameter("@Content", SqlDbType.NText){ Value = model.Content },
				new SqlParameter("@CreatorId", SqlDbType.Int,4){ Value = model.CreatorId },
				new SqlParameter("@CreateDate", SqlDbType.DateTime){ Value = model.CreateDate },
				new SqlParameter("@Editor", SqlDbType.Int,4){ Value = model.Editor },
				new SqlParameter("@EditDate", SqlDbType.DateTime){ Value = model.EditDate }
			};

            return DbHelperSQL.GetInt(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public bool Delete(int commentBaseId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CommentBase ");
            strSql.Append(" where CommentBaseId=@CommentBaseId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@CommentBaseId", SqlDbType.Int,4){ Value = commentBaseId }
			};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

		/// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public bool DeleteList(List<int> idList)
        {
            if (idList.Count == 0)
                return false;
            StringBuilder sbWhere = new StringBuilder();
            List<IDataParameter> lsPar = new List<IDataParameter>();
            for (int i = 0; i < idList.Count; i++)
            {
                string parameterName = "@Id" + i;
                sbWhere.AppendFormat("{0},", parameterName);
                lsPar.Add(new SqlParameter(parameterName, idList[i]));
            }
            sbWhere.Remove(sbWhere.Length - 1, 1);
            return DbHelperSQL.ExecuteSql(string.Format("delete from CommentBase where CommentBaseId in ({0})", sbWhere), lsPar.ToArray()) == idList.Count;
        }

		/// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(CommentBase model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CommentBase set TopicBaseId=@TopicBaseId,ReplyCommentBaseId=@ReplyCommentBaseId,Title=@Title,Content=@Content,CreatorId=@CreatorId,CreateDate=@CreateDate,Editor=@Editor,EditDate=@EditDate");            
            strSql.Append(" where  CommentBaseId=@CommentBaseId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = model.TopicBaseId },
				new SqlParameter("@ReplyCommentBaseId", SqlDbType.Int,4){ Value = model.ReplyCommentBaseId },
				new SqlParameter("@Title", SqlDbType.NVarChar,50){ Value = model.Title },
				new SqlParameter("@Content", SqlDbType.NText){ Value = model.Content },
				new SqlParameter("@CreatorId", SqlDbType.Int,4){ Value = model.CreatorId },
				new SqlParameter("@CreateDate", SqlDbType.DateTime){ Value = model.CreateDate },
				new SqlParameter("@Editor", SqlDbType.Int,4){ Value = model.Editor },
				new SqlParameter("@EditDate", SqlDbType.DateTime){ Value = model.EditDate }
			};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

		/// <summary>
        /// 是否存在行
        /// </summary>
        /// <returns></returns>
        public bool Exists(int commentBaseId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CommentBase where CommentBaseId=@CommentBaseId ");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@CommentBaseId", SqlDbType.Int,4){ Value = commentBaseId }
			};

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

		/// <summary>
        /// 获取数据实体
        /// </summary>
        /// <returns></returns>
        public CommentBase GetModel(int commentBaseId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from CommentBase where CommentBaseId=@CommentBaseId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@CommentBaseId", SqlDbType.Int,4){ Value = commentBaseId }
			};

            DataTable dtb = DbHelperSQL.GetDataTable(strSql.ToString(), parameters);
            if (dtb.Rows.Count > 0)
            {
                return CreateModel(dtb.Rows[0]);
            }
            return null;
        }

		/// <summary>
        /// 使用数据行创建数据实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private CommentBase CreateModel(DataRow dr)
        {
            CommentBase model = new CommentBase();
						model.CommentBaseId = ConvertUtil.ToInt(dr["CommentBaseId"]);
			model.TopicBaseId = ConvertUtil.ToInt(dr["TopicBaseId"]);
			model.ReplyCommentBaseId = ConvertUtil.ToInt(dr["ReplyCommentBaseId"]);
			model.Title = dr["Title"].ToString();
			model.Content = dr["Content"].ToString();
			model.CreatorId = ConvertUtil.ToInt(dr["CreatorId"]);
			model.CreateDate = ConvertUtil.ToDateTime(dr["CreateDate"]);
			model.Editor = ConvertUtil.ToInt(dr["Editor"]);
			model.EditDate = ConvertUtil.ToDateTime(dr["EditDate"]);
            
            return model;
        }

		/// <summary>
        /// 获取记录详情
        /// </summary>
        /// <returns></returns>
        public DataTable GetDetails(int commentBaseId)
        {
			IDataParameter[] parameters = 
			{
				new SqlParameter("@CommentBaseId", SqlDbType.Int,4){ Value = commentBaseId }
			};

            return DbHelperSQL.GetDataTable(@"select * from CommentBase where CommentBaseId=@CommentBaseId",parameters);
        }

		/// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="queryField">查询条件的字段名称集合，字段格式：a.FieldName,>,参数名附加字符</param>
        /// <param name="queryValue">查询条件的值集合</param>
        /// <param name="orderby">排序列名</param>
        /// <param name="index">页码，从1开始</param>
        /// <param name="size">页记录数</param>
        /// <param name="totalCount">输出的总记录行数</param>
        /// <returns></returns>
        public DataTable GetList(List<string> queryField, List<object> queryValue, string orderby, int index, int size, out int totalCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TT.* FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.CommentBaseId");
            }
            strSql.Append(")AS Row, T.*  from CommentBase T where 1=1");
            StringBuilder sbWhere = new StringBuilder();
            List<IDataParameter> parm = new List<IDataParameter>();
            if (queryField != null && queryField.Count > 0)
            {
                for (int i = 0; i < queryField.Count; i++)
                {
                    string qualifier = " = ";
                    string parameterAddin = "";
                    //最后次变量用于存储 判断限定符 左边 的 输入 如 a.FieldName
                    string fieldNameIncom = queryField[i];
                    string[] curArg = fieldNameIncom.Split(',');
                    if (curArg.Length > 1)
                    {
                        fieldNameIncom = curArg[0];
                        qualifier = curArg[1];
                    }
                    if (curArg.Length > 2)
                    {
                        parameterAddin = curArg[2];
                    }
                    string fieldName = fieldNameIncom;
                    int ditIndex = fieldName.IndexOf('.');
                    if (ditIndex > 0)
                    {
                        fieldName = fieldName.Substring(ditIndex + 1);
                    }
                    sbWhere.AppendFormat(" AND {0}{1}@{2}{3} ", fieldNameIncom, qualifier, fieldName, parameterAddin);
                    parm.Add(new SqlParameter("@" + fieldName + parameterAddin, queryValue[i]));
                }
            }
            totalCount = DbHelperSQL.GetInt(@"select count(*) from CommentBase as a where 1=1" + sbWhere, parm.ToArray());

            strSql.Append(sbWhere);
            strSql.Append(" ) as TT ");
            int pageIndex = (index - 1) * size;
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", pageIndex, pageIndex + size);

            return DbHelperSQL.GetDataTable(strSql.ToString(), parm.ToArray());
        }   
	}
}